<!DOCTYPE html>
<html lang=en>
<head>
    <!-- so meta -->
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="HandheldFriendly" content="True">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <meta name="description" content="获取表： select table_name from user_tables; &#x2F;&#x2F;当前用户的表        select table_name from all_tables; &#x2F;&#x2F;所有用户的表    select table_name from dba_tables; &#x2F;&#x2F;包括系统表  select table_name from dba_tables where owner&#x3D;’用户名’">
<meta property="og:type" content="article">
<meta property="og:title" content="oracle常用查询">
<meta property="og:url" content="http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/index.html">
<meta property="og:site_name" content="TmoonSite">
<meta property="og:description" content="获取表： select table_name from user_tables; &#x2F;&#x2F;当前用户的表        select table_name from all_tables; &#x2F;&#x2F;所有用户的表    select table_name from dba_tables; &#x2F;&#x2F;包括系统表  select table_name from dba_tables where owner&#x3D;’用户名’">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2017-03-04T01:12:24.000Z">
<meta property="article:modified_time" content="2020-01-07T10:48:14.857Z">
<meta property="article:author" content="Tmoonlight">
<meta name="twitter:card" content="summary">
    
    
        
          
              <link rel="shortcut icon" href="/images/favicon.ico">
          
        
        
          
            <link rel="icon" type="image/png" href="/images/logo2.gif" sizes="192x192">
          
        
        
          
            <link rel="apple-touch-icon" sizes="180x180" href="/images/logo2.gif">
          
        
    
    <!-- title -->
    <title>oracle常用查询</title>
    <!-- styles -->
    
<link rel="stylesheet" href="/css/style.css">

    <!-- persian styles -->
    
      
<link rel="stylesheet" href="/css/rtl.css">

    
    <!-- rss -->
    
    
<meta name="generator" content="Hexo 4.2.0"></head>

<body class="max-width mx-auto px3 ltr">
    
      <div id="header-post">
  <a id="menu-icon" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="menu-icon-tablet" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="top-icon-tablet" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');" style="display:none;"><i class="fas fa-chevron-up fa-lg"></i></a>
  <span id="menu">
    <span id="nav">
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </span>
    <br/>
    <span id="actions">
      <ul>
        
        <li><a class="icon" href="/2017/03/23/%E4%BD%BF%E7%94%A8NSSM%E5%B0%86exe%E5%B0%81%E8%A3%85%E4%B8%BA%E6%9C%8D%E5%8A%A1/"><i class="fas fa-chevron-left" aria-hidden="true" onmouseover="$('#i-prev').toggle();" onmouseout="$('#i-prev').toggle();"></i></a></li>
        
        
        <li><a class="icon" href="/2017/02/19/%E5%85%B1%E4%BA%AB%E9%A1%B9%E7%9B%AE(sharedproject)/"><i class="fas fa-chevron-right" aria-hidden="true" onmouseover="$('#i-next').toggle();" onmouseout="$('#i-next').toggle();"></i></a></li>
        
        <li><a class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up" aria-hidden="true" onmouseover="$('#i-top').toggle();" onmouseout="$('#i-top').toggle();"></i></a></li>
        <li><a class="icon" href="#"><i class="fas fa-share-alt" aria-hidden="true" onmouseover="$('#i-share').toggle();" onmouseout="$('#i-share').toggle();" onclick="$('#share').toggle();return false;"></i></a></li>
      </ul>
      <span id="i-prev" class="info" style="display:none;">Previous post</span>
      <span id="i-next" class="info" style="display:none;">Next post</span>
      <span id="i-top" class="info" style="display:none;">Back to top</span>
      <span id="i-share" class="info" style="display:none;">Share post</span>
    </span>
    <br/>
    <div id="share" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/" target="_blank" rel="noopener"><i class="fab fa-facebook " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&text=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-twitter " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-linkedin " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&is_video=false&description=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-pinterest " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=oracle常用查询&body=Check out this article: http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/"><i class="fas fa-envelope " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-get-pocket " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-reddit " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-stumbleupon " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-digg " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&name=oracle常用查询&description=" target="_blank" rel="noopener"><i class="fab fa-tumblr " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://news.ycombinator.com/submitlink?u=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&t=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-hacker-news " aria-hidden="true"></i></a></li>
</ul>

    </div>
    <div id="toc">
      <ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#获取表："><span class="toc-number">1.</span> <span class="toc-text">获取表：</span></a></li></ol>
    </div>
  </span>
</div>

    
    <div class="content index py4">
        
        <article class="post" itemscope itemtype="http://schema.org/BlogPosting">
  <header>
    
    <h1 class="posttitle" itemprop="name headline">
        oracle常用查询
    </h1>



    <div class="meta">
      <span class="author" itemprop="author" itemscope itemtype="http://schema.org/Person">
        <span itemprop="name">TmoonSite</span>
      </span>
      
    <div class="postdate">
      
        <time datetime="2017-03-04T01:12:24.000Z" itemprop="datePublished">2017-03-04</time>
        
      
    </div>


      

      

    </div>
  </header>
  

  <div class="content" itemprop="articleBody">
    <h1 id="获取表："><a href="#获取表：" class="headerlink" title="获取表："></a>获取表：</h1><p> select table_name from user_tables; //当前用户的表      </p>
<p> select table_name from all_tables; //所有用户的表  </p>
<p> select table_name from dba_tables; //包括系统表</p>
<p> select table_name from dba_tables where owner=’用户名’</p>
<p> user_tables：</p>
<p> table_name,tablespace_name,last_analyzed等</p>
<p> dba_tables：</p>
<p> ower,table_name,tablespace_name,last_analyzed等</p>
<p> all_tables：</p>
<p> ower,table_name,tablespace_name,last_analyzed等</p>
<p> all_objects：</p>
<p> ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等</p>
<p>获取表字段：</p>
<p> select * from user_tab_columns where Table_Name=’用户表’;</p>
<p> select * from all_tab_columns where Table_Name=’用户表’;</p>
<p> select * from dba_tab_columns where Table_Name=’用户表’;</p>
<p> user_tab_columns：</p>
<p> table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等</p>
<p> all_tab_columns ：</p>
<p> ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等</p>
<p> dba_tab_columns：</p>
<p> ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等</p>
<p>获取表注释：</p>
<p> select * from user_tab_comments</p>
<p> user_tab_comments：table_name,table_type,comments</p>
<p> --相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。</p>
<p>获取字段注释：</p>
<p> select * from user_col_comments</p>
<p> user_col_comments：table_name,column_name,comments</p>
<p>日期范围查询：</p>
<ol>
<li>select * from tablename where time&gt;=  </li>
<li>to_date(‘2011-06-02’,’yyyy-mm-dd’)    </li>
<li>and  time&lt;=to_date(‘2011-06-30’,’yyyy-mm-dd’) </li>
</ol>
<p>运行的结果是：可以显示06-02的数据，但是不能显示06-30的数据。</p>
<p>所有可以得出结论：</p>
<p>①如果想显示05-30的数据可以&lt;to_date(‘2011-05-31’,’yyyy-mm-dd’)，这样就能显示30号的了。</p>
<p>②如果想要显示05-30的数据可以&lt;=to_date(‘2011-05-30 23:59:59 999’,’yyyy-mm-dd hh24:mi:ss’)也是可以查出来的。  </p>
<p> <strong>最终解决方案:</strong></p>
<ol>
<li>select * from tablename where to_char(time,’yyyy-mm-dd’)&gt;=’2011-05-02’    </li>
<li>and to_char(time,’yyyy-mm-dd’)&lt;=’2011-05-30’ </li>
</ol>
<p>获取存储过程内容：</p>
<p>SELECT * FROM ALL_SOURCE@ecccsc where TYPE=’PROCEDURE’ and owner=’NEW_SUPPORT’ AND name LIKE ‘%INSERTNEWDOC%’ order by line</p>
<p> <strong>建一个job：</strong></p>
<p>declare job_id number ;</p>
<p>begin</p>
<p>  sys.dbms_job.submit(job_id,</p>
<pre><code>what =&gt; &apos;ECC_CSC.SYN_SL_PRODUCT;&apos;,

next_date =&gt; to_date( &apos;05-12-2014 12:00:00&apos;, &apos;dd-mm-yyyy hh24:mi:ss&apos; ),

interval =&gt; &apos;sysdate+1&apos; );</code></pre><p>end;</p>
<p>每天运行一次                        ‘SYSDATE + 1’</p>
<p>每小时运行一次                     ‘SYSDATE + 1/24’</p>
<p>每10分钟运行一次                 ‘SYSDATE + 10/（60*24）’</p>
<p>每30秒运行一次                    ‘SYSDATE + 30/(60<em>24</em>60)’</p>
<p>每隔一星期运行一次               ‘SYSDATE + 7’</p>
<p>每个月最后一天运行一次         ‘TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,1))) + 23/24’</p>
<p>每年1月1号零时                    ‘TRUNC(LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE)||’12’||’01’,’YYYY-MM-DD’))+1)’</p>
<p>每天午夜12点                       ‘TRUNC(SYSDATE + 1)’</p>
<p>每天早上8点30分                  ‘TRUNC(SYSDATE + 1) + (8<em>60+30)/(24</em>60)’</p>
<p>每星期二中午12点                 ‘NEXT_DAY(TRUNC(SYSDATE ), ‘’TUESDAY’’ ) + 12/24’</p>
<p>每个月第一天的午夜12点        ‘TRUNC(LAST_DAY(SYSDATE ) + 1)’</p>
<p>每个月最后一天的23点           ‘TRUNC (LAST_DAY (SYSDATE)) + 23 / 24’</p>
<p>每个季度最后一天的晚上11点  ‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24’</p>
<p>每星期六和日早上6点10分      ‘TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘’SATURDAY”), NEXT_DAY(SYSDATE, “SUNDAY”))) + (6<em>60+10)/(24</em>60)’</p>

  </div>
</article>



        
          <div id="footer-post-container">
  <div id="footer-post">

    <div id="nav-footer" style="display: none">
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </div>

    <div id="toc-footer" style="display: none">
      <ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#获取表："><span class="toc-number">1.</span> <span class="toc-text">获取表：</span></a></li></ol>
    </div>

    <div id="share-footer" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/" target="_blank" rel="noopener"><i class="fab fa-facebook fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&text=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-twitter fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-linkedin fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&is_video=false&description=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-pinterest fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=oracle常用查询&body=Check out this article: http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/"><i class="fas fa-envelope fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-get-pocket fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-reddit fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-stumbleupon fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&title=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-digg fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&name=oracle常用查询&description=" target="_blank" rel="noopener"><i class="fab fa-tumblr fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://news.ycombinator.com/submitlink?u=http://yoursite.com/2017/03/04/oracle%E5%B8%B8%E7%94%A8%E6%9F%A5%E8%AF%A2/&t=oracle常用查询" target="_blank" rel="noopener"><i class="fab fa-hacker-news fa-lg" aria-hidden="true"></i></a></li>
</ul>

    </div>

    <div id="actions-footer">
        <a id="menu" class="icon" href="#" onclick="$('#nav-footer').toggle();return false;"><i class="fas fa-bars fa-lg" aria-hidden="true"></i> Menu</a>
        <a id="toc" class="icon" href="#" onclick="$('#toc-footer').toggle();return false;"><i class="fas fa-list fa-lg" aria-hidden="true"></i> TOC</a>
        <a id="share" class="icon" href="#" onclick="$('#share-footer').toggle();return false;"><i class="fas fa-share-alt fa-lg" aria-hidden="true"></i> Share</a>
        <a id="top" style="display:none" class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up fa-lg" aria-hidden="true"></i> Top</a>
    </div>

  </div>
</div>

        
        <footer id="footer">
  <div class="footer-left">
    Copyright &copy; 2020 Tmoonlight
  </div>
  <div class="footer-right">
    <nav>
      <ul>
         
          <li><a href="/">Home</a></li>
         
          <li><a href="/about/">About</a></li>
         
          <li><a href="/archives/">Writing</a></li>
         
          <li><a href="/projects_url">Projects</a></li>
        
      </ul>
    </nav>
  </div>
</footer>

    </div>
    <!-- styles -->

<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">


<link rel="stylesheet" href="/lib/justified-gallery/css/justifiedGallery.min.css">


    <!-- jquery -->

<script src="/lib/jquery/jquery.min.js"></script>


<script src="/lib/justified-gallery/js/jquery.justifiedGallery.min.js"></script>

<!-- clipboard -->

  
<script src="/lib/clipboard/clipboard.min.js"></script>

  <script type="text/javascript">
  $(function() {
    // copy-btn HTML
    var btn = "<span class=\"btn-copy tooltipped tooltipped-sw\" aria-label=\"Copy to clipboard!\">";
    btn += '<i class="far fa-clone"></i>';
    btn += '</span>'; 
    // mount it!
    $(".highlight table").before(btn);
    var clip = new ClipboardJS('.btn-copy', {
      text: function(trigger) {
        return Array.from(trigger.nextElementSibling.querySelectorAll('.code')).reduce((str,it)=>str+it.innerText+'\n','')
      }
    });
    clip.on('success', function(e) {
      e.trigger.setAttribute('aria-label', "Copied!");
      e.clearSelection();
    })
  })
  </script>


<script src="/js/main.js"></script>

<!-- search -->

<!-- Google Analytics -->

<!-- Baidu Analytics -->

<!-- Disqus Comments -->


</body>
</html>
